﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WAD.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace WAD.DAL
{
    /// <summary>
    /// 广告数据库操作类
    /// </summary>
    public class DaoUser : DAO
    {
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public static bool Add(MUser m)
        {
            String sql = "insert into [user](name,pwd,nick_name,email,phone,qq,logo)values(@name,@pwd,@nick_name,@email,@phone,@qq,@logo)";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@name", DbType.String, m.name);
            db.AddInParameter(cmd, "@pwd", DbType.String, m.pwd);
            db.AddInParameter(cmd, "@nick_name", DbType.String, m.nick_name);
            db.AddInParameter(cmd, "@email", DbType.String, m.email);
            db.AddInParameter(cmd, "@phone", DbType.String, m.phone);
            db.AddInParameter(cmd, "@qq", DbType.String, m.qq);
            db.AddInParameter(cmd, "@logo", DbType.String, m.logo);
            return db.ExecuteNonQuery(cmd) > 0;
        }

        /// <summary>
        /// 修改用户基本信息
        /// </summary>
        /// <param name="m">哪个有值修改哪个</param>
        /// <returns></returns>
        public static int UpdateInfo(MUser m)
        {
            Database db = GetDatabase();
            DbCommand cmd = db.GetStoredProcCommand("UpdateInfo");

            db.AddInParameter(cmd, "@id", DbType.Int32, m.id);
            db.AddInParameter(cmd, "@nick_name", DbType.String, m.nick_name);
            db.AddInParameter(cmd, "@email", DbType.String, m.email);
            db.AddInParameter(cmd, "@phone", DbType.String, m.phone);
            db.AddInParameter(cmd, "@tel", DbType.String, m.tel);
            db.AddInParameter(cmd, "@qq", DbType.String, m.qq);
            db.AddInParameter(cmd, "@logo", DbType.String, m.logo);
            db.AddInParameter(cmd, "@parent_id", DbType.Int32, m.parent_id);
            db.AddInParameter(cmd, "@agentid", DbType.Int32, m.agentid);
            db.AddInParameter(cmd, "@articlelogo", DbType.String, m.articlelogo);
            db.AddInParameter(cmd, "@wx_code", DbType.String, m.wx_code);
            db.AddInParameter(cmd, "@wx_public_code", DbType.String, m.wx_public_code);

            db.AddOutParameter(cmd, "@code", DbType.Int32, 0);

            DataSet ds = db.ExecuteDataSet(cmd);
            int code = Convert.ToInt32(db.GetParameterValue(cmd, "@code"));
            return code;
        }

        public static void UpdateWxLoginInfo(MUser m)
        {
            var sql = "update [user] set wx_login_openid='"+m.wx_login_openid+"',wx_login_token='"+m.wx_login_token+ "' where api_rs = '" + m.api_rs + "'";
            ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 注册
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public static int Regist(MUser m)
        {
            Database db = GetDatabase();
            DbCommand cmd = db.GetStoredProcCommand("Regist");

            db.AddInParameter(cmd, "@name", DbType.String, m.name);
            db.AddInParameter(cmd, "@pwd", DbType.String, m.pwd);
            db.AddInParameter(cmd, "@nick_name", DbType.String, m.nick_name);
            db.AddInParameter(cmd, "@email", DbType.String, m.email);
            db.AddInParameter(cmd, "@phone", DbType.String, m.phone);
            db.AddInParameter(cmd, "@qq", DbType.String, m.qq);
            db.AddInParameter(cmd, "@logo", DbType.String, m.logo);
            db.AddInParameter(cmd, "@login_type", DbType.Int32, m.login_type);
            db.AddInParameter(cmd, "@api_rs", DbType.String, m.api_rs);
            db.AddInParameter(cmd, "@agentid", DbType.String, m.agentid);

            db.AddOutParameter(cmd, "@code", DbType.Int32, 0);

            DataSet ds = db.ExecuteDataSet(cmd);
            int code = Convert.ToInt32(db.GetParameterValue(cmd, "@code"));
            return code;
        }

        /// <summary>
        /// 登陆
        /// </summary>
        /// <param name="m"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static int Login(MUser m, out Dictionary<String, object> data)
        {
            Database db = GetDatabase();
            DbCommand cmd = db.GetStoredProcCommand("Login");

            db.AddInParameter(cmd, "@name", DbType.String, m.name);
            db.AddInParameter(cmd, "@pwd", DbType.String, m.pwd);
            db.AddInParameter(cmd, "@phone", DbType.String, m.phone);
            db.AddInParameter(cmd, "@login_type", DbType.Int32, m.login_type);
            db.AddInParameter(cmd, "@api_rs", DbType.String, m.api_rs);

            db.AddOutParameter(cmd, "@code", DbType.Int32, 0);

            DataSet ds = db.ExecuteDataSet(cmd);
            int code = Convert.ToInt32(db.GetParameterValue(cmd, "@code"));
            if (code == 0)
            {
                DataTable table = ds.Tables[0];

                DataColumnCollection cols = table.Columns;
                data = new Dictionary<string, object>();
                DataRow row = table.Rows[0];
                for (int i = 0; i < cols.Count; i++)
                {
                    data.Add(cols[i].ColumnName, row[i]);
                }
            }
            else
            {
                data = null;
            }
            return code;
        }

        /// <summary>
        /// 获取一个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MUser GetEntity(int id)
        {
            String sql = "select id,name,nick_name,[login_type],token,api_rs,[state],qq,phone,tel,is_validate_phone,wx_code,wx_public_code,wx_login_openid,wx_login_token from [user] where id = " + id;
            object obj = BindEntity(sql, new MUser());
            return obj == null ? null : (MUser)obj;
        }

        /// <summary>
        /// 获取一个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MUser GetEntityByName(String name)
        {
            String sql = "select id,name,[login_type],email,phone,pwd,token,[state] from [user] where name = '" + name + "'";
            object obj = BindEntity(sql, new MUser());
            return obj == null ? null : (MUser)obj;
        }

        /// <summary>
        /// 获取一个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MUser GetEntityByPhone(String phone)
        {
            String sql = "select id,name,[login_type],email,phone,is_validate_phone,pwd,token,[state] from [user] where phone = '" + phone + "'";
            object obj = BindEntity(sql, new MUser());
            return obj == null ? null : (MUser)obj;
        }

        /// <summary>
        /// 获取上级代理商信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="fileds"></param>
        /// <returns></returns>
        public static Dictionary<string, object> GetParentAgent(int id, string fileds = "*")
        {
            return BindMap(string.Format("select top 1 {0} from [user] u where id=(select top 1 u2.agentid from [user] u2 where u2.id={1}) ", fileds, id));
        }

        /// <summary>
        /// 是否已经存在了该手机号
        /// </summary>
        /// <param name="phone"></param>
        /// <returns></returns>
        public static bool IsSamePhone(String phone)
        {
            String sql = "select count(id) from [user] where phone = '" + phone + "'";
            int? num = (Int32)ExecuteScalar(sql);
            return num == null ? false : num > 0;
        }

        /// <summary>
        /// 获取用户的详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Dictionary<string, object> GetDetail(int id)
        {
            return GetDetail(id, "id,name,nick_name,email,phone,is_validate_phone,wx_code,wx_public_code,tel,qq,logo,parent_id");
        }

        public static Dictionary<string, object> GetDetail(int id, string fileds = "")
        {
            if (string.IsNullOrEmpty(fileds))
                fileds = "*";
            return BindMap(string.Format("select {0} from [user] where id ={1} ", fileds, id));
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示的总条数</param>
        /// <returns></returns>
        public static PageData GetList(int pageIndex, int pageSize, MUser m)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("1=1");
            if (m.id > 0)
                sb.Append(" and [id]=" + m.id);

            if (m.agentid > 0)
                sb.Append(" and agentid=" + m.agentid);

            if (!string.IsNullOrEmpty(m.name))
                sb.AppendFormat(" and name like'%{0}%'", m.name);

            if (!string.IsNullOrEmpty(m.nick_name))
                sb.AppendFormat(" and nick_name like'%{0}%'", m.nick_name);

            if (!string.IsNullOrEmpty(m.phone))
                sb.AppendFormat(" and phone like'%{0}%'", m.phone);

            String fieldList = "id,name,nick_name,email,phone,qq,logo,parent_id,agentid,[login_type],[state],CONVERT(nvarchar(20),[date],120) as [date],(select count(1) from user_version where [state] =1 and [user_id]=[user].id)as number";
            String orderBy = "id desc";
            String tableName = "[user]";
            return GetPageList(fieldList, orderBy, pageIndex, pageSize, sb.ToString(), tableName);
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id">广告id</param>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public static bool Delete(int id)
        {
            String sql = "delete [user] where id = " + id;
            return ExecuteNonQuery(sql) > 0;
        }

        /// <summary>
        /// 隐藏用户
        /// </summary>
        /// <param name="id">广告id</param>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public static bool UpdateState(int id, int state)
        {
            return ExecuteNonQuery(string.Format("update [user] set [state] ={0}  where id = {1}", state, id)) > 0;
        }

        /// <summary>
        /// 用户手机号验证通过
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static bool ValidatePhone(int userId, String phone)
        {
            string sql = "update [user] set is_validate_phone = 1 where id = " + userId + " and phone = '" + phone + "'";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            return db.ExecuteNonQuery(cmd) > 0;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="nickName"></param>
        /// <returns></returns>
        public static bool ModifyPwd(int? id, String oldPwd, String newPwd)
        {
            string sql = "update [user] set pwd =@pwd,token = '' where id =@id and pwd =@oldpwd";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@pwd", DbType.String, newPwd);
            db.AddInParameter(cmd, "@id", DbType.Int32, id);
            db.AddInParameter(cmd, "@oldpwd", DbType.String, oldPwd);
            return db.ExecuteNonQuery(cmd) > 0;
        }

        public static bool ModifyPwd(int? id, int login_type, String newPwd)
        {
            string sql = "update [user] set pwd =@pwd where id =@id and login_type =@login_type";
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@pwd", DbType.String, newPwd);
            db.AddInParameter(cmd, "@id", DbType.Int32, id);
            db.AddInParameter(cmd, "@login_type", DbType.Int32, login_type);
            return db.ExecuteNonQuery(cmd) > 0;
        }

        #region 发财模块:支付功能

        /// <summary>
        /// 更新余额
        /// </summary>
        /// <param name="id"></param>
        /// <param name="money"></param>
        /// <returns></returns>
        public static int UpdateBalance(int id, decimal money, int group)
        {
            string DES_KEY = "qd711688";
            string balance = QD.Utils.Encrypt.Encode(money.ToString(), DES_KEY);    //金额加密(DES加密)
            if (group == 1)
                return ExecuteNonQuery(string.Format("update [user] set ad_balance='{0}' where id={1}", balance, id));
            else if (group == 2)
                return ExecuteNonQuery(string.Format("update [user] set shared_balance='{0}' where id={1}", balance, id));
            return 0;
        }

        /// <summary>
        /// 余额DES解密
        /// </summary>
        /// <param name="money"></param>
        /// <param name="DES_KEY"></param>
        /// <returns></returns>
        public static decimal BalanceDncode(object money, string DES_KEY)
        {
            string s = Convert.ToString(money);
            if (string.IsNullOrEmpty(s))
                return 0;

            s = QD.Utils.Encrypt.Decode(s, DES_KEY); //DES解密
            if (string.IsNullOrEmpty(s))
                return 0;
            return Convert.ToDecimal(s);
        }
        #endregion

        #region 商汇通: 开放平台接口
        public static PageData GetListByEndDate(int pageIndex, int pageSize, MUser m,string start_date,string end_date)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("agentid>0 and datediff(dd,start_date,end_date)>30");

            if (m.id > 0)
                sb.Append(" and [id]=" + m.id);

            if (m.agentid > 0)
                sb.Append(" and agentid=" + m.agentid);

            if (!string.IsNullOrEmpty(m.name))
                sb.AppendFormat(" and name like'%{0}%'", m.name);

            if (!string.IsNullOrEmpty(m.nick_name))
                sb.AppendFormat(" and nick_name like'%{0}%'", m.nick_name);

            if (!string.IsNullOrEmpty(m.phone))
                sb.AppendFormat(" and phone like'%{0}%'", m.phone);

            if (!string.IsNullOrEmpty(start_date))
                sb.AppendFormat(" and datediff(dd,'{0}',start_date)>-1", start_date);

            if (!string.IsNullOrEmpty(end_date))
                sb.AppendFormat(" and datediff(dd,'{0}',start_date)<1", end_date);
           
            String fieldList = "id,name,nick_name,agentid,phone,qq,[date],[state],[start_date],end_date";
            String orderBy = "start_date desc";
            String tableName = "v_user#user_version##end_date";
            return GetPageList(fieldList, orderBy, pageIndex, pageSize, sb.ToString(), tableName);
        }
        #endregion
    }
}
